using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlServerCe;
using System.Data;
using System.Runtime.CompilerServices;

namespace FeedFly
{
    /// <summary>
    /// Static helper class for working with folders
    /// </summary>
    public static class Folder
    {
        public static SqlCeDataReader GetFolderDataReader(int folderId, SqlCeConnection conn)
        {                        
            return Data.ExecuteReader("Folder","Folder_ParentId", folderId, conn, false);
        }

        public static SqlCeDataReader GetFolderDataReader(int folderId)
        {
            return Data.ExecuteReader("Folder", "Folder_ParentId", folderId);
        }

        //public static SqlCeResultSet GetFolderResultSet(int folderId, SqlCeConnection conn)
        //{
        //    StringBuilder sql = new StringBuilder(200);
        //    sql.Append("SELECT id, Name, CASE WHEN unreadcount > 0 THEN name + ' (' + CONVERT(nvarchar(5),unreadcount) + ')' ELSE name END AS formattedName FROM Folder WHERE ParentId = ");
        //    return Data.ExecuteResultSet(sql.Append(folderId).ToString(), conn);
        //}

        //public static SqlCeResultSet GetFolderResultSetWithFeeds(int folderId, SqlCeConnection conn)
        //{
        //    StringBuilder sql = new StringBuilder(500);
        //    sql.Append("SELECT 1 as sort, id, parentId, Name, CASE WHEN unreadcount > 0 THEN name + ' (' + CONVERT(nvarchar(5),unreadcount) + ')' ELSE name END AS formattedName FROM Folder WHERE ParentId = ");
        //    sql.Append(folderId);
        //    sql.Append(" UNION SELECT 2, id, folderId, title, CASE WHEN unreadcount > 0 THEN title + ' (' + CONVERT(nvarchar(5),unreadcount) + ')' ELSE title END AS formattedTitle FROM Feed WHERE FolderId = ");
        //    sql.Append(folderId);
        //    sql.Append("ORDER BY sort, name");
        //    return Data.ExecuteResultSet(sql.ToString(), conn);
        //}

        public struct MarkRequest
        {
            public int FolderId;
            public bool IsRead;

            public MarkRequest(int folderId, bool isRead)
            {
                FolderId = folderId;
                IsRead = isRead;
            }
        }

        public static void Mark(MarkRequest mr)
        {            
            SqlCeConnection conn = Data.GetConnection();
            SqlCeTransaction trans = conn.BeginTransaction();            

            Mark(mr.FolderId, mr.IsRead, conn, trans);

            trans.Commit();
            conn.Close();
            trans.Dispose();
            conn.Dispose();        
        }

        private static void Mark(int folderId, bool isRead, SqlCeConnection conn, SqlCeTransaction trans)
        {            
            // call Mark for all children feeds
            SqlCeDataReader rdr = Feed.GetFeedDataReader(folderId);
            Feed.MarkRequest mr = new Feed.MarkRequest(0,folderId,isRead);

            while(rdr.Read())
            {
                mr.FeedId = rdr.GetInt32(0);
                Feed.Mark(mr, conn, trans);
            }

            // loop through all children folders and call Mark() for each one
            rdr = GetFolderDataReader(folderId, conn);

            while (rdr.Read())
                Mark(rdr.GetInt32(0), isRead, conn, trans);

            rdr.Close();
            rdr.Dispose();
        }

        public static void AdjustUnreadCount(int folderId, int count, SqlCeConnection conn, SqlCeTransaction trans)
        {
            SqlCeCommand cmd = new SqlCeCommand("UPDATE Folder SET UnreadCount = UnreadCount + @UnreadOffset WHERE Id = @FolderId", conn, trans);
            cmd.Parameters.Add(new SqlCeParameter("@UnreadOffset", SqlDbType.Int));
            cmd.Parameters.Add(new SqlCeParameter("@FolderId", SqlDbType.Int));

            AdjustUnreadCount(folderId, count, cmd, conn, trans);

            cmd.Dispose();
        }

        private static void AdjustUnreadCount(int folderId, int count, SqlCeCommand cmd, SqlCeConnection conn ,SqlCeTransaction trans)
        {          
            // set properties
            cmd.Parameters[0].Value = count;
            cmd.Parameters[1].Value = folderId;

            // update unread count for folder
            cmd.ExecuteNonQuery();

            // get parent folder id
            object parentIdValue = Folder.GetParent(folderId);

            // if parent is not null, update parent folder's unread count
            if (parentIdValue is int)
            {
                int parentId = (int)parentIdValue;

                // send update to UI for current folder
                Screens.Main.AdjustUnreadCount(new UnreadCountAdjustment(parentId, folderId, count, true));

                // recursively update parent folders
                AdjustUnreadCount(parentId, count, cmd, conn, trans);
            }
        }

        public static object GetParent(int folderId)
        {
            StringBuilder sql = new StringBuilder("SELECT ParentId FROM Folder WHERE Id = ", 50);
            return Data.ExecuteScalar(sql.Append(folderId).ToString());
        }

        public static int Create(string name, int parentId)
        {
            SqlCeConnection conn = Data.GetConnection();

            SqlCeCommand cmd = conn.CreateCommand();

            cmd.CommandText = "INSERT Folder (Name, ParentId, UnreadCount) VALUES (@Name,@ParentId,0)";
            cmd.Parameters.Add(new SqlCeParameter("@Name", name));
            cmd.Parameters.Add(new SqlCeParameter("@ParentId", parentId));

            cmd.ExecuteNonQuery();

            int id = Data.GetIdentity(conn);

            conn.Close();
            conn.Dispose();

            return id;
        }

        public static void DeleteChildren(int folderId)
        {
            SqlCeConnection conn = Data.GetConnection();

            // get collection of feeds for this folder
            SqlCeDataReader rdr = Feed.GetFeedDataReader(folderId, conn);

            while (rdr.Read())
                Feed.Delete(rdr.GetInt32(0), folderId, false);

            rdr.Close();

            // get collectin of children folders
            rdr = GetFolderDataReader(folderId, conn);

            // recursively download feeds
            while (rdr.Read())
                Delete(rdr.GetInt32(0), folderId, false);

            rdr.Close();
            rdr.Dispose();

            conn.Close();
            conn.Dispose();

            // refresh list
            Screens.Main.RefreshFolder(folderId);
        }   

        /// <summary>
        /// Deletes a folder
        /// </summary>
        /// <param name="folderId"></param>
        public static void Delete(int folderId, int parentId, bool updateUI)
        {
            SqlCeConnection conn = Data.GetConnection();
            SqlCeTransaction trans = conn.BeginTransaction();

            try
            {
                // prepare command object for deletions
                SqlCeCommand cmd = conn.CreateCommand();
                cmd.Transaction = trans;
                cmd.Parameters.Add(new SqlCeParameter("@FolderId", SqlDbType.Int));

                Delete(folderId, cmd, conn);

                if (updateUI)
                    Screens.Main.RefreshFolder(parentId);

                trans.Commit();
            }
            catch (Exception e)
            {
                StringBuilder msg = new StringBuilder("Could not delete folder!<br><br>", 200);
                Screens.Main.QueueNotification(new NotificationMessage(NotificationMessageType.Error, msg.Append(e.Message).ToString()));
                trans.Rollback();
            }
            finally
            {
                conn.Close();
                trans.Dispose();
                conn.Dispose();
            }
        }     

        /// <summary>
        /// Recursive method for deleting folder
        /// </summary>
        private static void Delete(int folderId, SqlCeCommand cmd, SqlCeConnection conn)
        {            
            // delete children folder collection first
            SqlCeDataReader rdr = GetFolderDataReader(folderId, conn);

            // recursively delete children folder first
            while (rdr.Read())
                Delete(rdr.GetInt32(0), cmd, conn);

            rdr.Close();
            rdr.Dispose();

            // set parameter value
            cmd.Parameters[0].Value = folderId;

            // delete feed items
            cmd.CommandText = "DELETE FROM FeedItem WHERE FeedId IN (SELECT Id FROM Feed WHERE FolderId = @FolderId)";
            cmd.ExecuteNonQuery();

            // delete feeds
            cmd.CommandText = "DELETE FROM Feed WHERE FolderId = @FolderId";
            cmd.ExecuteNonQuery();

            // delete folder
            cmd.CommandText = "DELETE FROM Folder WHERE Id = @FolderId";            
            cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// Downloads updates for all feeds within folder hierarchy
        /// </summary>
        /// <param name="folderId">folderId could be passed as a string</param>
        /// <remarks>
        /// This method is normally called asynchronously, which is why the signature conforms to a WaitCallBack
        /// </remarks>
        public static void DownloadUpdate(object folderId)
        {
            SqlCeConnection conn = Data.GetConnection();

            DownloadUpdate(Convert.ToInt32(folderId), conn);

            conn.Close();
            conn.Dispose();            
        }

        private static void DownloadUpdate(int folderId, SqlCeConnection conn)
        {
            // get collection of feeds for this folder
            SqlCeDataReader rdr = Feed.GetFeedDataReader(folderId, conn);
            
            // add feeds to download queue
            while (rdr.Read())
                Net.EnqueueDownload(Feed.GetFeed(rdr.GetInt32(0)));

            rdr.Close();            
            
            // get collectin of children folders
            rdr = GetFolderDataReader(folderId, conn);

            // recursively download feeds
            while (rdr.Read())
                DownloadUpdate(rdr.GetInt32(0), conn);

            rdr.Close();
            rdr.Dispose();
        }
    }
}
